﻿/*
    Copyright 2013 Bob Moss (bobbyjmoss@gmail.com)

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace OpenRAMS.Model
{
    public class UserRecord
    {
        //exposed attributes of a user
        public String userName { get; set; }
        public String userPass { get; set; }
        public String userRoles { get; set; }
        public String userFirstName { get; set; }
        public String userSecondName { get; set; }
        public String userEmail { get; set; }
        public String userJobTitle { get; set; }
        public String userDept { get; set; }
        public String colourCode { get; set; }
        public DateTime createdTimeStamp { get; set; }
        
        //internal class variables/arrays
        protected String[] attribNames;
        protected int numAttribs;
        private bool NewOrOld;
        protected String tableName;

        //database-related variables
        private SqlConnection dbConnection;
        private SqlDataReader dbReader;
        private SqlCommand dbCommand;
        private String dbInString;

        public UserRecord()
        {
            initHardCodedVals();
            NewOrOld = true;    //if true, is new record

            //Initialise with sensible defaults
            userName = "";
            userPass = "";
            userRoles = "staff";
            userFirstName = "";
            userSecondName = "";
            userEmail = "";
            userJobTitle = "";
            userDept = "";
            colourCode = "#FFFFFF";
            createdTimeStamp = Convert.ToDateTime(System.DateTime.Now.ToString());

            //reads in db connection string
            try { dbInString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; }
            catch (Exception e) { throw e; }  //this will cause error conditions later
        }

        public int Load(String uName)
        {
            String queryFull = "SELECT * FROM " + tableName + " ", queryCondition = "WHERE " + attribNames[0] + "='" + uName + "'"; //strings that'll handle query.
            int rCheck = 0; //used for error-checking later
            String[] outputVars = new String[numAttribs + 1];

            NewOrOld = false;   //importing in a new record

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //verify input username is valid
            if (uName == "" || uName == "*" || uName == " ") { return -1; }

            //build query condition & append to query
            queryFull += queryCondition;
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //initialise output array
            for (int i = 0; i < outputVars.Length; i++)
            {
                outputVars[i] = "";
            }

            //Import values from query into string array
            while (dbReader.Read())
            {
                if (rCheck > 0)
                {
                    return -1;  //if it's a primary key, only one result should be returned. Error if not the case
                }
                else
                {
                    int i = 0;  //iterator for output string
                    foreach (String att in attribNames)
                    {
                        try { outputVars[i] = dbReader[att].ToString(); }
                        catch { return -1; }
                        i++;
                    }
                }
                rCheck++;   //tracks number of results
            }

            //Verify that at least one result was returned
            if (rCheck < 1) { return -1; };

            //Import values from string array to class variables
            userName = outputVars[0];
            userPass = outputVars[1];
            userRoles = outputVars[2];

            userFirstName = outputVars[3];
            userSecondName = outputVars[4];
            userEmail = outputVars[5];
            userJobTitle = outputVars[6];
            userDept = outputVars[7];
            colourCode = outputVars[8];

            try { createdTimeStamp = DateTime.Parse(outputVars[9]); }
            catch { return -1; }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            NewOrOld = false;   //fail-safe

            return 0;
        }

        public int Save()
        {
            //Strings to handle query as it's built
            String queryFull = "", queryCondition="";

            //verify input username is valid
            if (userName == "" || userName == "*" || userName == " ") { return -1; }

            //is this a new record? If so, we need an INSERT statement. Else, an UPDATE
            if (NewOrOld == true)
            {
                queryFull = "INSERT INTO " + tableName + " VALUES (";
                queryFull += "'" + userName + "', ";
                queryFull += "'" + userPass + "', ";
                queryFull += "'" + userRoles + "',";
                queryFull += "'" + userFirstName + "',";
                queryFull += "'" + userSecondName + "',";
                queryFull += "'" + userEmail + "',";
                queryFull += "'" + userJobTitle + "',";
                queryFull += "'" + userDept + "',";
                queryFull += "'" + colourCode + "',";
                queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
            }
            else
            {
                //verify the record actually exists
                UserRecord test = new UserRecord();
                int initCheck = test.Load(userName);
                if (initCheck < 0) { return -1; }

                //build the UPDATE query
                queryFull = "UPDATE " + tableName + " SET ";
                queryCondition = "WHERE " + attribNames[0] + "='" + userName + "'";

                /* TO ADD TO DOCUMENTATION */
                //wot? no element '0'? This method does not update primary keys.
                //this is because a) the database prevents this by design
                //& b) we don't want orphaned entries.

                //to change a username, the following should happen
                //a) load in user record & verify it's the one you expected
                //b) Load in any associated bookings & work tickets (they'll load in any dependencies)
                //c) Run delete method for loaded bookings & work tickets (keep them in memory though)
                //d) Run delete method on existing user record
                //e) Use class setters to set username for loaded user, booking & work ticket objects
                //f) Run save method for user object, THEN for bookings & work ticket

                queryFull += attribNames[1] + "='" + userPass + "', ";
                queryFull += attribNames[2] + "='" + userRoles + "', ";
                queryFull += attribNames[3] + "='" + userFirstName + "', ";
                queryFull += attribNames[4] + "='" + userSecondName + "', ";
                queryFull += attribNames[5] + "='" + userEmail + "', ";
                queryFull += attribNames[6] + "='" + userJobTitle + "', ";
                queryFull += attribNames[7] + "='" + userDept + "', ";
                queryFull += attribNames[8] + "='" + colourCode + "', ";
                queryFull += attribNames[9] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "' ";
                queryFull += queryCondition;
            }

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            //check if saved correctly if new
            if (NewOrOld == true)
            {
                int saveCheck = Load(userName);
                if (saveCheck < 0) { return -1; }
            }

            NewOrOld = false;   //fail-safe
            return 0;
        }


        /* TO ADD TO DOCUMENTATION */
        // Delete does not take parameters. This is on purpose.
        // Before deleting a record it's important to load it in & verify this is what you want to do
        // Records that have child entries in other tables will throw an SQL error as these are needed for stats purposes
        public int Delete()
        {
            //Use with caution! This will fail if orphaned entries exist
            String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribNames[0] + "='" + userName + "'";

            //verify input username is valid
            if (userName == "" || userName == "*" || userName == " ") { return -1; }

            //Check record exists in the first place
            UserRecord test = new UserRecord();
            int initCheck = test.Load(userName);
            if (initCheck < 0) { return -1; }

            /* TO DO - UNCOMMENT AND TEST LINES BELOW */
            // block delete if there are any bookings created by or assigned to user
            //Booking bookingNew = new Booking();
            //int bookCreatorCheck = bookingNew.Search(3, userName);    //uses attribID & string as parameters
            //int bookAssignCheck = bookingNew.Search(1, userName);
            //if(bookCreatorCheck > -1 || bookAssignCheck > -1) { return -1; }

            //...likewise for work tickets
            //WorkTicket wtNew = new WorkTicket();
            //int wtCreatorCheck = wtNew.Search(1, userName);
            //int wtAssignCheck = wtNew.Search(3, userName);
            //if(wtCreatorCheck > -1 || wtAssignCheck > -1) { return -1; }

            //...and any requests
            //RoomRequest rrNew = new RoomRequest();
            //int rrCheck = rrNew.Search(2, userName);
            //if(rrCheck > -1) { return -1; }

            /* TO-DO: Make sure txtUserName is actually stored in resource request table */
            //ResourceRequest rsrNew = New ResourceRequest();
            //int rsrCheck = rsrNew.Search(2, userName);
            //if(rsrCheck > -1) { return -1; }

            //...and any log entries
            //WorkTicketLog wtlNew = new WorkTicketLog();
            //int wtlCheck = wtlNew.Search(3, userName);
            //if(wtlCheck > -1) { return -1; }

            queryFull += queryCondition; //pull query together

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Check was deleted successfully
            int delCheck = Load(userName);
            if (delCheck > -1) { return -1; }

            NewOrOld = true;    //as the record no longer exists in the database, is considered 'new'

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            return 0;
        }

        protected void initHardCodedVals()
        {
            //set hard-coded values
            numAttribs = 9;
            tableName = "tblUsers";
            attribNames = new String[numAttribs+1];
            attribNames[0] = "txtUserName"; //primary key value
            attribNames[1] = "txtPassword";
            attribNames[2] = "txtRoles";
            attribNames[3] = "txtFirstName";
            attribNames[4] = "txtSecondName";
            attribNames[5] = "txtEmailAddress";
            attribNames[6] = "txtJobTitle";
            attribNames[7] = "txtDepartment";
            attribNames[8] = "txtColourCode";
            attribNames[9] = "dtTimeStamp";
        }
    }
}